package com.ejuornal.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.ejuornal.bean.Category;
import com.ejuornal.bean.Page;
import com.ejuornal.bean.PerPeriodical;
import com.ejuornal.bean.PerPeriodicalSub;
import com.ejuornal.bean.Periodical;
import com.ejuornal.dao.PerPeriodicalDao;
import com.mysql.jdbc.Statement;

@Component("perPeriodicalDao")
public class PerPeriodicalDaoImpl extends DaoSupport<PerPeriodical> implements
		PerPeriodicalDao {

	public void add(String sql, Object... objs) {
		super.add(sql, objs);
	}
	
	public int add(final  PerPeriodical perPeriodical) {
		KeyHolder keyHolder = new GeneratedKeyHolder();  
		
		this.getJdbcTemplate().update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection connection)
						throws SQLException {
					 String sql = "INSERT INTO t_per_periodical(number, introduce,keywords,status,periodicalId) VALUES(?, ?, ?, ?, ?)";
					 PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);  
					 ps.setInt(1, perPeriodical.getNumber());
					 ps.setString(2, perPeriodical.getIntroduce());
					 ps.setString(3, perPeriodical.getKeywords());
					 ps.setString(4, perPeriodical.getStatus());
					 ps.setInt(5, perPeriodical.getPeriodicalId());
					return ps;
				}
			},
			keyHolder
		);
		int generatedId = (int) keyHolder.getKey().intValue();
		return generatedId;
	}
	
	public void update(String sql, Object... objs) {
		super.update(sql, objs);
	}

	public void batchDelete(String sql, int... ids) {
		super.batchDelete(sql, ids);
	}

	public PerPeriodical findById(int perPeriodicalId) {
		String sql = "select *  from t_per_periodical where perPeriodicalId = ?";
		PerPeriodical perPeriodical = super.getJdbcTemplate().queryForObject(sql,
				new Object[] { perPeriodicalId }, new PerPeriodicalMapper());
		return perPeriodical;
	}

	public List<PerPeriodical> findNew(int count) {
		String sql = "select * from (select a.perPeriodicalId, a.number, b.name, b.cover, a.periodicalId from t_per_periodical a,  t_periodical b where a.periodicalId = b.periodicalId and a.status=1 and b.status=1 order by a.number desc) as c GROUP BY c.periodicalId limit 0,?";
		List<PerPeriodical> perPeriodicals = getJdbcTemplate().query(sql,
				new Object[] { count }, new RowMapper<PerPeriodical>() {

					public PerPeriodical mapRow(ResultSet rs, int arg1)
							throws SQLException {
						PerPeriodical p = new PerPeriodical();
						p.setPerPeriodicalId(rs.getInt("perPeriodicalId"));
						p.setNumber(rs.getInt("number"));
						p.setPeriodicalId(rs.getInt("periodicalId"));
						Periodical periodical = new Periodical();
						periodical.setCover(rs.getString("cover"));
						periodical.setName(rs.getString("name"));
						p.setPeriodical(periodical);
						return p;
					}
				});
		return perPeriodicals;
	}
	
	public List<PerPeriodical> findTop(int count) {
		String sql = "SELECT a.perPeriodicalId, a.number, a.introduce, b.name, b.cover  FROM t_per_periodical a join t_periodical b on a.periodicalId=b.periodicalId WHERE a.status=1 AND b.status=1 ORDER BY a.clickCount DESC LIMIT 0,?";
		List<PerPeriodical> periodicals = super.getJdbcTemplate().query(sql,
				new Object[] { count }, new RowMapper<PerPeriodical>() {
					public PerPeriodical mapRow(ResultSet rs, int arg1)
							throws SQLException {
						PerPeriodical p = new PerPeriodical();
						p.setIntroduce(rs.getString("introduce"));
						p.setPerPeriodicalId(rs.getInt("perPeriodicalId"));
						p.setNumber(rs.getInt("number"));
						Periodical periodical = new Periodical();
						periodical.setCover(rs.getString("cover"));
						periodical.setName(rs.getString("name"));
						p.setPeriodical(periodical);
						return p;
					}
				});
		return periodicals;
	}

	public PerPeriodical findUpNextPerPeriodical(int periodicalId, int number) {
		String sql =  "SELECT * FROM t_per_periodical WHERE periodicalId=? AND number=?";
		PerPeriodical perPeriodical = super.getJdbcTemplate().queryForObject(sql,
				new Object[] { periodicalId, number }, new PerPeriodicalMapper());

		return perPeriodical;
	}
	
	public List<PerPeriodical> findPerPeriodicalNumbers(int periodicalId, String status) {
		String sql = "SELECT * FROM t_per_periodical WHERE periodicalId=? AND status=? ORDER BY perPeriodicalId desc";
		
		return super.getJdbcTemplate().query(sql, new Object[]{ periodicalId, status }, new PerPeriodicalMapper());
	}

	@Override
	public PerPeriodical findNewPerPeriodical(int periodicalId, String status) {
		String  sql = " select * from t_per_periodical where  periodicalId = ? and status = ? order by perPeriodicalId  desc limit 1  ";
		PerPeriodical pp = getJdbcTemplate().queryForObject(sql, new Object[]{periodicalId, status }, new PerPeriodicalMapper());
		return pp;
	}
	
	public Page<PerPeriodical> find(int current_record, String url, String sql_1, String sql_2, Object[] objs) {
		return find(current_record, url, 10, sql_1, sql_2, objs);
	}
	
	public Page<PerPeriodical> find(int current_record, String url, int pageSize, String sql_1, String sql_2, Object[] objs) {
		Page<PerPeriodical> page = new Page<PerPeriodical>();
        @SuppressWarnings("deprecation")
		int totalRecords = this.getJdbcTemplate().queryForInt(sql_1, objs);
		List<PerPeriodical> list = (List<PerPeriodical>) this.getJdbcTemplate().query(sql_2, objs, new RowMapper<PerPeriodical>(){

			@Override
			public PerPeriodical mapRow(ResultSet rs, int rowNum) throws SQLException {
				Category category = new Category();
				category.setName(rs.getString("cname"));
				
				Periodical periodical = new Periodical();
				periodical.setName(rs.getString("name"));
				periodical.setCycle(rs.getString("cycle"));
				periodical.setCreateDate(rs.getDate("pubDate"));
				periodical.setCategory(category);
				
				PerPeriodical perPeriodical = new PerPeriodical();
				perPeriodical.setPerPeriodicalId(rs.getInt("perPeriodicalId"));
				perPeriodical.setNumber(rs.getInt("number"));

				perPeriodical.setPeriodical(periodical);
				
				return perPeriodical;
			}});
        page.setPageSize(pageSize);
        page.setUrl(url);
        page.setTotalRecords(totalRecords);
        page.setTotalPage(totalRecords);
        page.setRecords(list);
		
		return page;
	}

	@Override
	public List<PerPeriodicalSub> findBystatus(int status) {
		String sql = "select * from t_per_periodical_sub where status=1";
		List<PerPeriodicalSub> ppss = getJdbcTemplate().query(sql,
				new RowMapper<PerPeriodicalSub>() {
					@Override
					public PerPeriodicalSub mapRow(ResultSet rs, int arg1)
							throws SQLException {
						PerPeriodicalSub pps = new PerPeriodicalSub();
						pps.setId(rs.getInt("id"));
						pps.setPeriodicalId(rs.getInt("periodicalId"));
						pps.setPerPeriodicalid(rs.getInt("perPeriodicalid"));
						return pps;
					}
				});
		return ppss;
	}
	
	@Override
	public void add(PerPeriodicalSub pps) {
		String sql = "INSERT INTO t_per_periodical_sub(perPeriodicalid,periodicalId, status,createDate) value(?,?,1, now())";
		getJdbcTemplate().update(sql, new Object[] {
				pps.getPerPeriodicalid(),
				pps.getPeriodicalId()
		});
	}

	@Override
	public void update(PerPeriodicalSub pps) {
		String sql = "UPDATE t_per_periodical_sub SET status=0 where id=?";
		getJdbcTemplate().update(sql, new Object[] {pps.getId()});
		
	}
	
	public List<PerPeriodical> findPerPerEditor(int userId, String status) {
		String sql = "SELECT p.name,p.pubDate,pp.number,pp.perPeriodicalId FROM t_editor AS e INNER JOIN t_periodical AS p ON e.periodicalId=p.periodicalId AND p.status=1 INNER JOIN t_per_periodical AS pp ON p.periodicalId=pp.periodicalId WHERE e.userId=? AND pp.status=?";
		List<PerPeriodical> perPeriodicals = super.getJdbcTemplate().query(sql, new Object[]{ userId, status }, new RowMapper<PerPeriodical>() {

			@Override
			public PerPeriodical mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Periodical periodical = new Periodical();
				periodical.setName(rs.getString("name"));
				periodical.setPubDate(rs.getString("pubDate"));
				
				PerPeriodical perPeriodical = new PerPeriodical();
				perPeriodical.setPerPeriodicalId(rs.getInt("perPeriodicalId"));
				perPeriodical.setNumber(rs.getInt("number"));
				perPeriodical.setPeriodical(periodical);
				
				return perPeriodical;
			}
		});
		
		return perPeriodicals;
	}
}

final class PerPeriodicalMapper implements RowMapper<PerPeriodical> {

	public PerPeriodical mapRow(ResultSet rs, int rowNum) throws SQLException {
		PerPeriodical perPeriodical = new PerPeriodical();
		perPeriodical.setIntroduce(rs.getString("introduce"));
		perPeriodical.setKeywords(rs.getString("keywords"));
		perPeriodical.setNumber(rs.getInt("number"));
		perPeriodical.setPeriodicalId(rs.getInt("periodicalId"));
		perPeriodical.setPerPeriodicalId(rs.getInt("perPeriodicalId"));
		perPeriodical.setClickCount(rs.getInt("clickCount"));
		perPeriodical.setStatus(rs.getString("status"));
		perPeriodical.setCheckResult(rs.getString("checkResult"));

		return perPeriodical;
	}
	
	
}